﻿using System;
using System.Collections.Generic;
using System.Linq;
using LPY.LeHealth.DTO;
using System.Data;
using System.Data.SqlClient;
using LPY.LeHealth.Model;
namespace LPY.LeHealth.DAL
{
    using ServiceStack.OrmLite;
    using System.Configuration;

    public  class PhysicalDAL:BaseDAL
    {
        # region 体检项目管理

        public List<PhysicalItemDTO> GetPhysicalItemList(int pageIndex, int pageSize, out long totalSize)
        {
            using (var cnn = DbConnection)
            {

                var sql = cnn.From<PhysicalItemDTO>()
                    .OrderByDescending(m => m.CreateTime)
                    .Skip((pageIndex - 1) * pageSize)
                    .Take(pageSize);

                totalSize = cnn.Count<PhysicalItemDTO>();

                var rtv = new List<PhysicalItemDTO>();

                try
                {
                    rtv = cnn.Select<PhysicalItemDTO>(sql);
                }
                catch (Exception)
                {
                    throw;
                }

                return rtv;
            }
        }

        /// <summary>
        /// 添加体检项目
        /// </summary>
        public bool AddPhysicalItem(PhysicalItemDTO dto)
        {
            using (var cnn=DbConnection)
            {
                var rtv= cnn.Insert<PhysicalItemDTO>(dto);
                return rtv > 0;
            }
        }

        /// <summary>
        /// 根据ID获取体检项
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PhysicalItemDTO GetPhysicalItemById(int id)
        {
            using (var cnn=DbConnection)
            {
                return cnn.SingleById<PhysicalItemDTO>(id);
            }
        }

        //更新（编辑）
        public bool UpdatePhysicalItem(PhysicalItemDTO dto)
        {
            using (var cnn=DbConnection)
            {
                var data= cnn.SingleById<PhysicalItemDTO>(dto.Id);
                data.Name = dto.Name;
                data.Price = dto.Price;
                data.Purpose = dto.Purpose;
                var result = cnn.Update<PhysicalItemDTO>(data);
                 
               return result > 0;
            }
        }

        //（根据Id）删除
        public bool DeletePhysicalItemById(int id)
        {
            using (var cnn=DbConnection)
            {
                var result = cnn.DeleteById<PhysicalItemDTO>(id);
                return result > 0;
            }
        }

        #endregion

        /// <summary>
        /// 获取套餐列表
        /// </summary>
        /// <param name="single">是否单项：true-单项体检 false-体检套餐  null-所有体检</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalSize"></param>
        /// <returns></returns>
        public List<ComboDTO> GetComboList(bool single, int pageIndex,int pageSize, out long totalSize)
        {
            using (var cnn = DbConnection)
            {
                var sql = cnn.From<ComboDTO>()
                    .Where(m=>m.Single==single);
               
                totalSize = cnn.Count<ComboDTO>(sql);

                sql= sql.OrderByDescending(m => m.CreateTime)
                    .Skip((pageIndex - 1) * pageSize)
                    .Take(pageSize);

                var rtv = new List<ComboDTO>();

                try
                {
                    // rtv = cnn.LoadSelect<ComboDTO>(sql);//Hospital嵌套的数据加载不了
                    rtv= cnn.Select<ComboDTO>(sql);
                    var hos= cnn.Select<HospitalDTO>();
                    var areas= cnn.Select<AreaDTO>();
                    hos.Merge(areas);
                    rtv.Merge(hos);
                }
                catch (Exception)
                {
                    throw;
                }

                return rtv;
            }
        }

        public List<ComboDTO> GetComboList(int comboTypeId, int pageIndex, int pageSize, out long totalSize)
        {
            using (var cnn = DbConnection)
            {
                SqlExpression<ComboDTO> sql;
                if(comboTypeId<=0)
                {
                   sql = cnn.From<ComboDTO>();
                }
                else
                {
                    sql = cnn.From<ComboDTO>()
                        .Where(m => m.ComboTypeId == comboTypeId);
                }

                totalSize = cnn.Count<ComboDTO>(sql);

                sql = sql.OrderByDescending(m => m.CreateTime)
                    .Skip((pageIndex - 1) * pageSize)
                    .Take(pageSize);

                var rtv = new List<ComboDTO>();

                try
                {
                    rtv = cnn.LoadSelect<ComboDTO>(sql);//Hospital嵌套的数据加载不了
                    var hos = cnn.Select<HospitalDTO>();
                    var areas = cnn.Select<AreaDTO>();
                    hos.Merge(areas);
                    rtv.Merge(hos);
                }
                catch (Exception)
                {
                    throw;
                }

                return rtv;
            }
        }

        /// <summary>
        /// 获取所有体检类型
        /// </summary>
        /// <returns></returns>
        public List<ComboTypeDTO> GetAllComboType()
        {
            using (var cnn=DbConnection)
            {
               var rtv=  cnn.Select<ComboTypeDTO>();
               return rtv;
            }
        }

        /// <summary>
        /// 获取所有体检项列表
        /// </summary>
        /// <returns></returns>
        public List<PhysicalItemDTO> GetAllPhysicalItem()
        {
            using (var cnn=DbConnection)
            {
                var rtv = cnn.Select<PhysicalItemDTO>();
                return rtv;
            }
        }

        /// <summary>
        /// 根据医院Id获取体检项列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<PhysicalItemDTO> GetPhysicalsByHospitalId(int id)
        {
            using (var cnn=DbConnection)
            {
                var sql = cnn.From<PhysicalItemDTO>()
                    .Where(m => m.HospitalId == id);

                var rtv= cnn.Select<PhysicalItemDTO>(sql);
                return rtv;
            }
        }

        /// <summary>
        /// 根据Id删除套餐
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteComboById(int id)
        {
            using (var cnn=DbConnection)
            {
                //删除套餐项数据
                var sql = cnn.From<ComboToPhysicalItemDTO>()
                    .Where(m => m.ComboId == id);
                cnn.Delete<ComboToPhysicalItemDTO>(sql);

                //删除套餐数据
                var where = cnn.From<ComboDTO>()
                    .Where(m => m.Id == id);

                var result = cnn.Delete<ComboDTO>(where);
                return result > 0;
            }
        }

        /// <summary>
        /// 添加体检套餐
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="items"></param>
        /// <returns></returns>
        public bool AddCombo(ComboDTO dto, List<int> items)
        {
            using (var cnn=DbConnection)
            {
                var id= cnn.Insert<ComboDTO>(dto, selectIdentity: true);

                List<ComboToPhysicalItemDTO> refs = new List<ComboToPhysicalItemDTO>();
                ComboToPhysicalItemDTO r;
                foreach (var item in items)
                {
                    r = new ComboToPhysicalItemDTO();
                    r.ComboId = (int)id;
                    r.PhysicalItemId = item;
                    refs.Add(r);
                }

                cnn.InsertAll<ComboToPhysicalItemDTO>(refs);

                return true;
            }
        }

        //编辑体检套餐
        public bool EditCombo(ComboDTO dto,List<int> items)
        {
            using (var cnn=DbConnection)
            {
                var source = cnn.Select<ComboDTO>(cnn.From<ComboDTO>().Where(m=>m.Id==dto.Id)).FirstOrDefault();
                if (null == source)
                    return false;

                source.Name = dto.Name;
                source.Price = dto.Price;
                source.Appropriate = dto.Appropriate;
                source.ComboTypeId = dto.ComboTypeId;
                source.Details = dto.Details;
                source.HospitalId = dto.HospitalId;

                cnn.Update<ComboDTO>(source);

                cnn.Delete<ComboToPhysicalItemDTO>(cnn.From<ComboToPhysicalItemDTO>().Where(m => m.ComboId == dto.Id));

                List<ComboToPhysicalItemDTO> refs = new List<ComboToPhysicalItemDTO>();
                ComboToPhysicalItemDTO r;
                foreach (var item in items)
                {
                    r = new ComboToPhysicalItemDTO();
                    r.ComboId = (int)dto.Id;
                    r.PhysicalItemId = item;
                    refs.Add(r);
                }

                cnn.InsertAll<ComboToPhysicalItemDTO>(refs);

                return true;
            }
        }

        /// <summary>
        /// 根据Id获取套餐信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ComboDTO GetCombo(int id)
        {
            using (var cnn = DbConnection)
            {
                var combo = cnn.Select<ComboDTO>(cnn.From<ComboDTO>().Where(m => m.Id == id)).FirstOrDefault();
                var hos = cnn.Select<HospitalDTO>(cnn.From<HospitalDTO>().Where(m=>m.Id==combo.HospitalId)).FirstOrDefault();
                //var areas = cnn.Select<AreaDTO>();
                //hos.Merge(areas);
                var comboType= cnn.Select<ComboTypeDTO>(cnn.From<ComboTypeDTO>().Where(m => m.Id == combo.ComboTypeId)).FirstOrDefault();
                combo.Hospital=hos;
                combo.ComboType = comboType;
                return combo;
            }
        }

        /// <summary>
        /// 获取套餐内的体检项
        /// </summary>
        /// <param name="comboId"></param>
        /// <returns></returns>
        public List<ComboToPhysicalItemDTO> GetComboToPhysicalItems(int comboId)
        {
            using (var cnn=DbConnection)
            {
                var items = cnn.Select<ComboToPhysicalItemDTO>(cnn.From<ComboToPhysicalItemDTO>().Where(m => m.ComboId == comboId));
                return items;
            }
        }

        /// <summary>
        /// 获取子项详细信息
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<PhysicalItemDTO> GetPhysicalItems(List<int> ids)
        {
            using (var cnn=DbConnection)
            {
                var result= cnn.SelectByIds<PhysicalItemDTO>(ids);
                return result;
            }
        }
        /// <summary>
        /// 文件上传
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="fileurl"></param>
        /// <returns></returns>
        public bool AddPhysicitemFile(PhysicalItemFiles fiels)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();

                string sqlstr = "insert into PhysicalItemFile values('"+fiels.PhysicalItemFileName+"','"+fiels.PhysicalItemFileUrl+"','"+fiels.PhysicalItemType+"')";
                SqlCommand cmd = new SqlCommand(sqlstr, conn);

                return cmd.ExecuteNonQuery() > 0;

            }
        }
        /// <summary>
        /// 显示所有要导入的体检类型文件
        /// </summary>
        /// <returns></returns>
        public List<PhysicalItemFiles>ShowFile(string id)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();
                if(id==null)
                {
                    string sqlstr = "select * from PhysicalItemFile";
                    SqlCommand cmd = new SqlCommand(sqlstr, conn);
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataTable table = new DataTable();
                    adp.Fill(table);
                    List<PhysicalItemFiles> fileurl = new List<PhysicalItemFiles>();
                    foreach (DataRow item in table.Rows)
                    {
                        PhysicalItemFiles files = new PhysicalItemFiles();
                        files.PhysicalItemFIlesId = Convert.ToInt32(item["PhysicalItemFIlesId"]);
                        files.PhysicalItemFileName = item["PhysicalItemFileName"].ToString();
                        files.PhysicalItemFileUrl = item["PhysicalItemFileUrl"].ToString();

                        fileurl.Add(files);

                    }
                    return fileurl;
                }
                else
                {
                    string sqlstr = "select * from PhysicalItemFile where PhysicalItemFIlesId='"+id+"'";
                    SqlCommand cmd = new SqlCommand(sqlstr, conn);
                    SqlDataAdapter adp = new SqlDataAdapter(cmd);
                    DataTable table = new DataTable();
                    adp.Fill(table);
                    List<PhysicalItemFiles> fileurl = new List<PhysicalItemFiles>();
                    foreach (DataRow item in table.Rows)
                    {
                        PhysicalItemFiles files = new PhysicalItemFiles();
                        files.PhysicalItemFIlesId = Convert.ToInt32(item["PhysicalItemFIlesId"]);
                        files.PhysicalItemFileName = item["PhysicalItemFileName"].ToString();
                        files.PhysicalItemFileUrl = item["PhysicalItemFileUrl"].ToString();
                        files.PhysicalItemType = int.Parse(item["PhysicalItemType"].ToString());
                        fileurl.Add(files);

                    }
                    return fileurl;
                }

            }
        }
        /// <summary>
        /// 添加体检项目
        /// </summary>
        /// <param name="phyname"></param>
        /// <param name="porpuse"></param>
        /// <param name="price"></param>
        /// <param name="hospitalid"></param>
        /// <param name="typeid"></param>
        /// <returns></returns>
        public bool InsertPhysical(string phyname,string porpuse,float price,int hospitalid,int typeid)

        {
             string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();

                string sqls = "insert into t_physicalitemdto values('"+phyname+"','"+porpuse+"','"+price+"','"+hospitalid+"','"+DateTime.Now.ToString()+"','"+typeid+"')";
                SqlCommand cmd = new SqlCommand(sqls,conn);
                return cmd.ExecuteNonQuery() > 0;
            }

        }
        /// <summary>
        /// 文件导入完后,删除数据库中的文件信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeletePhysical(int id)
        {
             string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();

                string delstr = "delete from PhysicalItemFile where PhysicalItemFIlesId='"+id+"'";
                SqlCommand cmd = new SqlCommand(delstr, conn);
                return cmd.ExecuteNonQuery() > 0;
            }
        }
        /// <summary>
        /// 批量添加套餐项目
        /// </summary>
        /// <param name="comb"></param>
        /// <returns></returns>
        public bool AddComboItem(Combo comb,decimal OriginalPrice)
        {
             string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();
                string insecomb = "insert into t_combo values('"+comb.Price+"','"+OriginalPrice+"','"+comb.Name+"','"+comb.Details+"','"+comb.HospitalId+"','"+comb.Appropriate+"','"+comb.ComboTypeId+"',"+0+",'"+DateTime.Now.ToString()+"','"+comb.Oldprice+"','"+comb.Sonnum+"','"+null+"','"+comb.Scheck+"')";

                SqlCommand cmd = new SqlCommand(insecomb, conn);

                return cmd.ExecuteNonQuery() > 0;
            }
        }

        /// <summary>
        /// 根据项目类型名称查找对应的ID
        /// </summary>
        /// <returns></returns>
        public int ShowTypeid(string typename)
        {
             string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using(SqlConnection conn=new SqlConnection(constring))
            {
                conn.Open();

                string sqlselect = "select Id from t_physicalitemtype where Name='"+typename+"'";

                SqlCommand cmd = new SqlCommand(sqlselect,conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable table = new DataTable();

                adp.Fill(table);

                int nameid=int.Parse(table.Rows[0][0].ToString());
                return nameid;
            }
        }
        /// <summary>
        /// 根据套餐类型名称查找对应的类型ID
        /// </summary>
        /// <param name="comname"></param>
        /// <returns></returns>
        public int Comtypid(string comname)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();

                string sqlselect = "select Id from t_combotype where Name='" + comname + "'";

                SqlCommand cmd = new SqlCommand(sqlselect, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable table = new DataTable();

                adp.Fill(table);

                int nameid = int.Parse(table.Rows[0][0].ToString());
                return nameid;
            }
        }
        /// <summary>
        /// 根据医院名称查找对应的医院ID
        /// </summary>
        /// <returns></returns>
        public int ShowHospitalId(string hospitalname)
        {

            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();

                string sqlselect = "select Id from t_hospital where Name='" + hospitalname + "'";

                SqlCommand cmd = new SqlCommand(sqlselect, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable table = new DataTable();

                adp.Fill(table);

                int nameid = int.Parse(table.Rows[0][0].ToString());
                return nameid;
            }
        }
        /// <summary>
        /// 查找套餐表中当前插入行的ID以及体检项目
        /// </summary>
        /// <returns></returns>
        public List<string> ShowComtable()
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();

                string sqlselect = "select top 1 Id,Scheck from t_combo order by Id desc";

                SqlCommand cmd = new SqlCommand(sqlselect, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                DataTable table = new DataTable();

                adp.Fill(table);

                List<string> list = new List<string>();
                string nameid = table.Rows[0][0].ToString();
                string Scheck = table.Rows[0][1].ToString();

                list.Add(nameid);
                list.Add(Scheck);

                return list;
            }
        }

        /// <summary>
        /// 根据套餐ID查找套餐及项目关系表中是否存在此套餐信息
        /// </summary>
        /// <param name="combid"></param>
        /// <returns></returns>
        public DataTable ShowComPrhyItem(int combid)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();

                string sstre = "select ComboId from t_combotophysicalitem where ComboId="+combid+"";

                SqlCommand cmd = new SqlCommand(sstre,conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                adp.Fill(table);
                return table;
            }
        }
        /// <summary>
        /// 查找体检项目对应的ID
        /// </summary>
        /// <param name="dtoname"></param>
        /// <returns></returns>
        public int Showphysicalitemdto(string dtoname)
        {
            string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();

                string sstre = "select Id from t_physicalitemdto where Name='" + dtoname + "'";

                SqlCommand cmd = new SqlCommand(sstre, conn);

                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                adp.Fill(table);

                int comb = int.Parse(table.Rows[0][0].ToString());
               
                return comb;
            }
        }
        /// <summary>
        /// 循环添加t_combotophysicalitem表
        /// </summary>
        /// <param name="combid"></param>
        /// <param name="PhysicalItemId"></param>
        /// <returns></returns>
        public bool Addt_combotophysicalitem(int combid,int PhysicalItemId)
        {
             string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();//定义公共数据库连接串

            using (SqlConnection conn = new SqlConnection(constring))
            {
                conn.Open();

                string sqladd = "insert into t_combotophysicalitem values(" + combid + "," + PhysicalItemId + ")";
                SqlCommand cmd = new SqlCommand(sqladd, conn);
                return cmd.ExecuteNonQuery() > 0;
            }
        }

    }
}
